home *** CD-ROM | disk | FTP | other *** search
/ Micom Basic 1995 October / CD [BM9510].bin / programs / lotus / lotus001.dsk / DATALENS / DLNCAT.SQL < prev    next >
Encoding:
Text File  |  1993-10-14  |  1.4 KB  |  51 lines
  1. rem dlncat.sql
  2. rem
  3. rem Create DATALENS_OBJECTS view on ORACLE v6 system tables.
  4. rem
  5.  
  6. drop view DATALENS_OBJECTS
  7. /
  8. create view DATALENS_OBJECTS
  9.     (OBJ_OWNER,
  10.      OBJ_NAME,
  11.      OBJ_TYPE,
  12.      OBJ_COMMENT,
  13.      SYN_TAB_OWNER,
  14.      SYN_TAB_NAME,
  15.      NUM_OF_COL)
  16. as
  17. select u.name,
  18.        o.name,
  19.        decode(o.type, 2, 1,                     /* table */
  20.                       4, 2,                     /* view */
  21.                       5, decode(owner#, 1, 4,   /* public synonym */
  22.                                            3    /* synonym */ ),
  23.                          NULL),
  24.        c.comment$,
  25.        decode(o.type, 5, s.owner,
  26.                          NULL),
  27.        decode(o.type, 5, s.name,
  28.                          NULL),
  29.        decode(o.type, 2, t.cols,
  30.                       4, v.cols, 0)
  31. from sys.obj$ o, sys.user$ u, sys.com$ c, sys.tab$ t, sys.view$ v, sys.syn$ s
  32. where o.owner# = u.user#
  33.   and o.obj# = t.obj#(+)
  34.   and o.obj# = v.obj#(+)
  35.   and o.obj# = c.obj#(+)
  36.   and o.obj# = s.obj#(+)
  37.   and c.col#(+) is null
  38.   and o.type in (2,4,5)
  39.   and (o.owner# = uid
  40.        or
  41.        o.obj# in (select obj#
  42.                     from sys.tabauth$
  43.                     where grantee# in (uid, 1)))
  44. /
  45. drop public synonym DATALENS_OBJECTS
  46. /
  47. create public synonym DATALENS_OBJECTS for DATALENS_OBJECTS
  48. /
  49. grant select on DATALENS_OBJECTS to PUBLIC
  50. /
  51.